﻿using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace ESAM_Assignment_3
{
    /// <summary>
    /// This class contains methods to dynamically create the database and tables required for this assignment. Also, this class also contain a method to remove the database as well. 
    /// The codes within this class is STRICTLY NOT to be modified. 
    /// </summary>
    public class DBManager
    {
        private static string Database_Name = Common.Database_Name;
        private static string connectionString = "data source.\\SQLEXPRESS; initial catalog=" + Database_Name + "; integrated security=true";

        #region Aesthetics features - The method will generate a random quote to be inserted as a comment. | This segment of codes is NOT to be modified.
        //Declare the Random outside the method to ensure the number generated is unlikely to be duplicated
        private static Random QuoteRandom = new Random();

        /// <summary>
        /// Method to display a random chosen quote. This is the longer version. 
        /// </summary>
        /// <returns></returns>
        protected static string GenerateQuote()
        {
            string[] Quotes = {"Walking on water and developing software from a specification are easy if both are frozen. (Unknown)", 
                              "If debugging is the process of removing software bugs, then programming must be the process of putting them in. (Edsger Dijkstra)",
                              "I have not failed. I have just found 10,000 ways that will not work. (Thomas A. Edison)",
                              "The best thing about a boolean is even if you are wrong, you are only off by a bit. (Unknown)",
                              "I do not care if it works on your machine!  We are not shipping your machine! (Vidiu Platon)",
                              "I would love to change the world, but they will not give me the source code. (Unknown)",
                              "If people never did silly things, nothing intelligent would ever get done. (Ludwig Wittgenstein)",
                              "\"Press any key to continue\" Ehh.... where is the ANY key??? (Unknown)",
                              "I think it is a new feature.  Do not tell anyone it was an accident. (Larry Wall)",
                              "The only truly secure system is one that is powered off, cast in a block of concrete and sealed in a lead-lined room with armed guards. (Gene Spafford)",
                              "Passwords are like underwear: you do not let people see it, you should change it very often, and you should not share it with strangers. (Chris Pirillo)",
                              "The mantra of any good security engineer is: \"Security is a not a product, but a process.\" It is more than designing strong cryptography into a system; it is designing the entire system such that all security measures, including cryptography, work together. (Bruce Schneier)",
                              "I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image. (Stephen Hawking)",
                              "Securing a computer system has traditionally been a battle of wits: the penetrator tries to find the holes, and the designer tries to close them. (Gosser)",
                              "If you think technology can solve your security problems, then you do not understand the problems and you do not understand the technology. (Bruce Schneier)",
                              "In God we trust. All others, we virus scan (Unknown)",
                              "The whole notion of passwords is based on an oxymoron. The idea is to have a random string that is easy to remember. Unfortunately, if it is easy to remember, it is something nonrandom like \"Susan\". And if it is random, like \"r7U2*Qnp\", then it is not easy to remember. (Bruce Schneier)",
                              "Phishing is a major problem because there really is no patch for human stupidity. (Mike Danseglio)",
                              "The most likely way for the world to be destroyed, most experts agree, is by accident. That is where we come in; we are computer professionals. We cause accidents. (Nathaniel Borenstein)"
                          };

            return Quotes[QuoteRandom.Next(0, Quotes.Length)];
        }
        #endregion

        #region Advanced SQL Statements | This segment of codes is NOT to be modified.
        private static string admin_connectionString = "data source=.\\SQLEXPRESS; initial catalog=tempdb; integrated security=true";

        /// <summary>
        /// Method to dynamically create the database: ESAM_ASSIGNMENT
        /// </summary>
        /// <returns></returns>
        public static string CreateDatabase()
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the ESAM Assignment database
            try
            {
                myConn.ConnectionString = admin_connectionString;
                myConn.Open();

                strQuery = "CREATE DATABASE " + Database_Name;
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                //Reset all connection pooling, otherwise there will be a transport error if we recreate the database/table later.
                SqlConnection.ClearAllPools();

                return "Database has been created successfully";
            }
            catch (SqlException sqlex)
            {
                if (sqlex.Number == 1801)
                {
                    return "Database exists! No action taken.";
                }
                else
                {
                    return sqlex.Message;
                }
            }
            catch (Exception ex)
            {

                return ex.Message;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to create the users in the table: Users 
        /// </summary>
        /// <returns></returns>
        public static string CreateUsersTable()
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the USERS table in the newly created database
            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "USE " + Database_Name;
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                strQuery = "CREATE TABLE Users (UserID INT IDENTITY(1,1), Username VARCHAR(50), Fullname VARCHAR(200),  Password VARCHAR(100), CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserID] ASC) ) ON [PRIMARY]";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                strQuery = "CREATE UNIQUE NONCLUSTERED INDEX Unq_Username ON Users(Username)";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                strQuery = "INSERT INTO Users (Username, Fullname, Password) VALUES ('anonymous', 'Anonymous User', 'esam2011')";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                Random rand = new Random();
                for (int i = 1; i <= 5; i++)
                {
                    strQuery = "INSERT INTO Users (Username, Fullname, Password) VALUES ('user" + i + "', 'Fullname " + i + "', 'user" + i + "')";
                    myCmd = new SqlCommand(strQuery, myConn);
                    myCmd.ExecuteNonQuery();
                }

                return "USERS Table has been created in the Database successfully";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to create test data in the table: Comments
        /// </summary>
        /// <returns></returns>
        public static string CreateCommentsTable()
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the COMMENTS table in the newly created database
            try
            {
                myConn.ConnectionString = connectionString;
                myConn.Open();

                strQuery = "USE " + Database_Name;
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                strQuery = "CREATE TABLE Comments (CommentID INT IDENTITY(1,1), Comment VARCHAR(500), Username VARCHAR(50), DatePosted DATETIME, Subject VARCHAR(100), Modified VARCHAR(1), CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED ([CommentID] ASC) ) ON [PRIMARY]";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                Random rand = new Random();

                int j = 1;
                string CommentFiller = "";

                for (int i = 1; i <= 10; i++)
                {
                    #region Insert HTML Tags
                    int HTML_Tag = rand.Next(1, 4);
                    switch (HTML_Tag)
                    {
                        case 1:
                            CommentFiller = GenerateQuote();
                            break;
                        case 2:
                            CommentFiller = "<b>" + GenerateQuote() + "</b>";
                            break;
                        case 3:
                            CommentFiller = "<i>" + GenerateQuote() + "</i>";
                            break;
                    }
                    #endregion

                    #region Randomly indicate comment to be NEW or MODIFIED
                    string IsModified = "";
                    if ((rand.Next(1, 29) % 2) == 0)
                    {
                        IsModified = "n";
                    }
                    else
                    {
                        IsModified = "y";
                    }
                    #endregion

                    if (i == 6)
                    {
                        j = 1;
                    }

                    string FakeDateTime = rand.Next(1, 27) + "-" + System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.MonthNames[rand.Next(0, 12)].Substring(0, 3) + "-2010 " + rand.Next(0, 24) + ":" + rand.Next(0, 60);

                    strQuery = "INSERT INTO Comments (Comment, Username, DatePosted, Subject, Modified) VALUES ('User " + j + " - " + CommentFiller.Replace("'", "''") + "', 'user" + j + "', '" + FakeDateTime + "', 'My Subject #" + i + "', '" + IsModified + "')";
                    j += 1;
                    myCmd = new SqlCommand(strQuery, myConn);
                    myCmd.ExecuteNonQuery();
                }

                return "COMMENTS Table has been created in the Database successfully";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to create test data in the table: Comments
        /// </summary>
        /// <returns></returns>
        public static string CreateRelationship()
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the COMMENTS table in the newly create database
            try
            {
                myConn.ConnectionString = connectionString;
                myConn.Open();

                strQuery = "USE " + Database_Name;
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                strQuery = "ALTER TABLE Comments WITH CHECK ADD CONSTRAINT [FK_Comments_Users (Username)] FOREIGN KEY([Username]) REFERENCES Users ([Username])";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                strQuery = "ALTER TABLE Comments CHECK CONSTRAINT [FK_Comments_Users (Username)]";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                return "Relationships between tables has been created in the Database successfully";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to remove the database: ESAM_ASSIGNMENT
        /// </summary>
        /// <returns></returns>
        public static string DropDatabase()
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //If the database exist, drop it.
            try
            {
                myConn.ConnectionString = admin_connectionString;
                myConn.Open();

                strQuery = "SELECT Count(*) from master..sysdatabases WHERE name = '" + Database_Name + "'";
                myCmd = new SqlCommand(strQuery, myConn);
                int nRet = (int)myCmd.ExecuteScalar();

                if (nRet > 0)
                {
                    strQuery = "ALTER DATABASE " + Database_Name + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
                    myCmd = new SqlCommand(strQuery, myConn);
                    myCmd.ExecuteNonQuery();

                    strQuery = "DROP DATABASE " + Database_Name;
                    myCmd = new SqlCommand(strQuery, myConn);
                    myCmd.ExecuteNonQuery();

                    return "Database has been dropped successfully";
                }
                else
                {
                    return "No Database to drop???";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to check if the database has been created by the student
        /// </summary>
        /// <returns></returns>
        public static bool IsDatabaseExist()
        {
            bool status = false;
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //If the database exist, drop it.
            try
            {
                myConn.ConnectionString = admin_connectionString;
                myConn.Open();

                strQuery = "SELECT Count(*) from master..sysdatabases WHERE name = '" + Database_Name + "'";
                myCmd = new SqlCommand(strQuery, myConn);
                int nRet = (int)myCmd.ExecuteScalar();

                if (nRet > 0)
                {
                    status = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myConn.Close();
            }

            return status;
        }
        #endregion
    }
}